package com.qdlc.p2p.dal.dao.impl;


import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Service;

import com.qdlc.p2p.common.constant.Global;
import com.qdlc.p2p.common.util.Page;
import com.qdlc.p2p.dal.common.dao.jpa.BaseDaoImpl;
import com.qdlc.p2p.dal.common.model.jpa.PageDataList;
import com.qdlc.p2p.dal.dao.EnjoyBorrowDao;
import com.qdlc.p2p.dal.dto.EnjoyBorrow;

/**
 * Created by D on 2016/1/20.
 */
@Service("enjoyBorrowDao")
public class EnjoyBorrowDaoImpl extends BaseDaoImpl<EnjoyBorrow> implements EnjoyBorrowDao {

    @Override
    public EnjoyBorrow findEnjoyGoodsByBorrowId(long borrowId) {
        String jpql = "from EnjoyBorrow where borrow.id=?1";
        Query query = em.createQuery(jpql).setParameter(1, borrowId);
        List<EnjoyBorrow> list = query.getResultList();
        if (list != null && list.size() >= 0) {
            return list.get(0);
        } else {
            return null;
        }
    }

    @Override
    public PageDataList<EnjoyBorrow> getList(int prefecture, int page) {
        String jpql = "from EnjoyBorrow where enjoyGoods.prefecture=?1 and borrow.status in (1,3,6,7,8)";
        Query query = em.createQuery(jpql).setParameter(1, prefecture);
        query.setFirstResult((page - 1) * EnjoyBorrow.Rows);
        query.setMaxResults(EnjoyBorrow.Rows);
        List<EnjoyBorrow> list = query.getResultList();
        Page page_ = new Page(list.size(), page, EnjoyBorrow.Rows);
        PageDataList<EnjoyBorrow> pageDataList = new PageDataList<EnjoyBorrow>();
        pageDataList.setPage(page_);
        pageDataList.setList(list);
        return pageDataList;
    }

    @Override
    public void giveGoods(int amount, long id) {
        String jpql = "UPDATE EnjoyBorrow SET sendout = sendout + :amount WHERE id = :id";
        Query query = em.createQuery(jpql);
        query.setParameter("amount", amount);
        query.setParameter("id", id);
        query.executeUpdate();
        super.refresh(find(id));
    }

    @Override
    public EnjoyBorrow findByPreAndPos(int prefecture, int position) {
        StringBuilder selectSql = new StringBuilder("SELECT p.* FROM ( SELECT eb.* FROM " + Global.DB_PREFIX + "borrow p1, " + Global.DB_PREFIX + "additional_enjoy_borrow eb, " + Global.DB_PREFIX + "additional_enjoy_goods eg WHERE p1. STATUS = 1 AND p1.id = eb.borrow_id AND eb.goods_id = eg.id AND eg.prefecture =");
        selectSql.append(prefecture+" AND eg.position = "+position+" AND p1.scales < 100 AND p1.type = 105 AND UNIX_TIMESTAMP(NOW()) > UNIX_TIMESTAMP(p1.put_start_time) ORDER BY p1.add_time DESC LIMIT 1 ) p UNION ALL SELECT p.* FROM ( SELECT eb.* FROM " + Global.DB_PREFIX + "borrow p1, " + Global.DB_PREFIX + "additional_enjoy_borrow eb, " + Global.DB_PREFIX + "additional_enjoy_goods eg WHERE p1. STATUS = 1 AND p1.id = eb.borrow_id AND eb.goods_id = eg.id AND eg.prefecture = ");
        selectSql.append(prefecture+" AND eg.position = "+position+" AND p1.scales < 100 AND p1.type = 105 AND UNIX_TIMESTAMP(NOW()) < UNIX_TIMESTAMP(p1.put_start_time) ORDER BY p1.add_time ASC LIMIT 1 ) p");

        Query query = em.createNativeQuery(selectSql.toString(), EnjoyBorrow.class);

        List<EnjoyBorrow> list = query.getResultList();
        if(list!=null && list.size()>0){
            return  list.get(0);
        }
        return null;
    }

    @Override
    public EnjoyBorrow findAllByPreAndPos(int prefecture, int position) {
        StringBuilder selectSql = new StringBuilder("SELECT qeb.* FROM " + Global.DB_PREFIX + "borrow qb, " + Global.DB_PREFIX + "additional_enjoy_borrow qeb, " + Global.DB_PREFIX + "additional_enjoy_goods qeg WHERE qb.id = qeb.borrow_id AND qeb.goods_id = qeg.id AND qb.`status` IN (1, 3, 6, 7, 8) AND qeg.prefecture = ");
        selectSql.append(prefecture+" AND qeg.position = ");
        selectSql.append(position+" ORDER BY qb.put_start_time DESC");
        Query query = em.createNativeQuery(selectSql.toString(), EnjoyBorrow.class);

        List<EnjoyBorrow> list = query.getResultList();
        if(list!=null && list.size()>0){
            return  list.get(0);
        }
        return null;
    }
}
